﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using iShoppingDTO;

namespace iShoppingDAO
{
    public class SanPhamDAO
    {
        public static SanPhamDTO getSanPhamOfMa(String maSanPham)
        {
            SanPhamDTO sp = null;
            try
            {

                string sql = "SELECT * FROM SanPham WHERE MaSanPham= '" + maSanPham + "'";
                DataTable dt = DataProvider.ExecuteQuery(sql);

                int i, n = dt.Rows.Count;
                for (i = 0; i < n; i++)
                {
                    sp = new SanPhamDTO();
                    sp.MaSanPham = dt.Rows[i]["MaSanPham"].ToString();
                    sp.TenSanPham = dt.Rows[i]["TenSanPham"].ToString();
                    sp.MaLoaiSanPham = dt.Rows[i]["MaLoaiSanPham"].ToString();
                    sp.MaTaiKhoan = dt.Rows[i]["MaTaiKhoan"].ToString();
                    sp.GiaKhoiDiem = int.Parse(dt.Rows[i]["GiaKhoiDiem"].ToString());
                    sp.GiaHienTai = int.Parse(dt.Rows[i]["GiaHienTai"].ToString());
                    sp.NgayDang = DateTime.Parse(dt.Rows[i]["NgayDang"].ToString());
                    sp.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan"].ToString());
                    sp.MaTinhTrangSP = int.Parse(dt.Rows[i]["MaTinhTrangSP"].ToString());
                    sp.HinhAnh = dt.Rows[i]["HinhAnh"].ToString();
                    sp.MoTaSanPham = dt.Rows[i]["MoTaSanPham"].ToString();
                    sp.SoLuotDatGia = int.Parse(dt.Rows[i]["SoLuotDatGia"].ToString());
                }

            }
            catch (Exception ex)
            {
                sp = new SanPhamDTO();
            }
            return sp;
        }
        public static DataTable getSanPhamOfMaSP(String maSanPham)
        {
            DataTable dataTable = new DataTable();
            try
            {

                string sql = "SELECT * FROM SanPham WHERE MaSanPham= '" + maSanPham + "'";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable; ;
        }

        public static DataTable getSanPhamOfMaSanPham(String maSanPham)
        {
            DataTable dataTable = new DataTable();
            try
            {

                string sql = "SELECT * FROM SanPham sp,TaiKhoan tk, ThongTinTK tks WHERE MaSanPham = '" + maSanPham + "' AND sp.MaTaiKhoan = tk.MaTaiKhoan AND tk.MaTaiKhoan = tks.MaTaiKhoan";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable; ;
        }
        public static DataTable getTopSanPham(DateTime ngayHetHan)
        {
            DataTable dataTable = new DataTable();
            try
            {

                String sql = "SELECT TOP (5) MaSanPham,TenSanPham,MaLoaiSanPham,MaTaiKhoan,GiaKhoiDiem,";
                sql += "GiaHienTai,NgayDang,NgayHetHan,MaTinhTrangSP,HinhAnh FROM SanPham WHERE MaTinhTrangSP = 1";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable; ;
        }

        public static DataTable getTopBySoLuotDatGia(int top)
        {
            DataTable dataTable = new DataTable();
            try
            {
                DateTime date = new DateTime();
                String sql = "SELECT TOP (" + top + ") MaSanPham,TenSanPham,MaLoaiSanPham,MaTaiKhoan,GiaKhoiDiem,";
                sql += "GiaHienTai,NgayDang,NgayHetHan,MaTinhTrangSP,HinhAnh, SoLuotDatGia FROM SanPham ";
                sql += "WHERE MaTinhTrangSP = 1 ORDER BY SoLuotDatGia DESC";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable;
        }

        public static DataTable getTopByDate(int top)
        {
            DataTable dataTable = new DataTable();
            try
            {
                DateTime date = new DateTime();
                String sql = "SELECT TOP (" + top + ") MaSanPham,TenSanPham,MaLoaiSanPham,MaTaiKhoan,GiaKhoiDiem,";
                sql += "GiaHienTai,NgayDang,NgayHetHan,MaTinhTrangSP,HinhAnh, SoLuotDatGia FROM SanPham ";
                sql += "WHERE MaTinhTrangSP = 1 ORDER BY NgayDang DESC";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable;
        }

        public static DataTable getToSanPhamOfLoai(string maloaiSP)
        {
            DataTable dataTable = new DataTable();
            try
            {

                String sql = "SELECT * FROM SanPham WHERE MaLoaiSanPham = '" + maloaiSP + "'";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable; ;
        }

        public List<SanPhamDTO> getToSanPhamOfLoaiSP(string maloaiSP)
        {
            List<SanPhamDTO> sanPhamDtos = new List<SanPhamDTO>();
            try
            {

                String sql = "SELECT * FROM SanPham WHERE MaLoaiSanPham = '" + maloaiSP + "'";
                DataTable dt = DataProvider.ExecuteQuery(sql);

                int i, n = dt.Rows.Count;
                for (i = 0; i < n; i++)
                {
                    SanPhamDTO sp = new SanPhamDTO();
                    sp.MaSanPham = dt.Rows[i]["MaSanPham"].ToString();
                    sp.TenSanPham = dt.Rows[i]["TenSanPham"].ToString();
                    sp.MaLoaiSanPham = dt.Rows[i]["MaLoaiSanPham"].ToString();
                    sp.MaTaiKhoan = dt.Rows[i]["MaTaiKhoan"].ToString();
                    sp.GiaKhoiDiem = int.Parse(dt.Rows[i]["GiaKhoiDiem"].ToString());
                    sp.GiaHienTai = int.Parse(dt.Rows[i]["GiaHienTai"].ToString());
                    sp.NgayDang = DateTime.Parse(dt.Rows[i]["NgayDang"].ToString());
                    sp.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan"].ToString());
                    sp.MaTinhTrangSP = int.Parse(dt.Rows[i]["MaTinhTrangSP"].ToString());
                    sp.HinhAnh = dt.Rows[i]["HinhAnh"].ToString();
                    sp.SoLuotDatGia = int.Parse(dt.Rows[i]["SoLuotDatGia"].ToString());
                    sanPhamDtos.Add(sp);
                }


            }
            catch (Exception ex)
            {
                sanPhamDtos = new List<SanPhamDTO>();
            }
            return sanPhamDtos; ;
        }


        public List<SanPhamDTO> getToSanPhamOfTenSP(string tenSanPham)
        {
            List<SanPhamDTO> sanPhamDtos = new List<SanPhamDTO>();
            try
            {

                String sql = "SELECT * FROM SanPham WHERE TenSanPham like '%' + @TenSanPham + '%'";

                List<SqlParameter> sqlParameters = new List<SqlParameter>();

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@TenSanPham", SqlDbType.NVarChar);
                par[0].Value = tenSanPham;
                sqlParameters.Add(par[0]);

                DataTable dt = DataProvider.ExecuteQuery(sql, sqlParameters);

                int i, n = dt.Rows.Count;
                for (i = 0; i < n; i++)
                {
                    SanPhamDTO sp = new SanPhamDTO();
                    sp.MaSanPham = dt.Rows[i]["MaSanPham"].ToString();
                    sp.TenSanPham = dt.Rows[i]["TenSanPham"].ToString();
                    sp.MaLoaiSanPham = dt.Rows[i]["MaLoaiSanPham"].ToString();
                    sp.MaTaiKhoan = dt.Rows[i]["MaTaiKhoan"].ToString();
                    sp.GiaKhoiDiem = int.Parse(dt.Rows[i]["GiaKhoiDiem"].ToString());
                    sp.GiaHienTai = int.Parse(dt.Rows[i]["GiaHienTai"].ToString());
                    sp.NgayDang = DateTime.Parse(dt.Rows[i]["NgayDang"].ToString());
                    sp.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan"].ToString());
                    sp.MaTinhTrangSP = int.Parse(dt.Rows[i]["MaTinhTrangSP"].ToString());
                    sp.HinhAnh = dt.Rows[i]["HinhAnh"].ToString();
                    sp.SoLuotDatGia = int.Parse(dt.Rows[i]["SoLuotDatGia"].ToString());
                    sanPhamDtos.Add(sp);
                }


            }
            catch (Exception ex)
            {
                sanPhamDtos = new List<SanPhamDTO>();
            }
            return sanPhamDtos; ;
        }
        public DataTable getTopSanPhams(DateTime ngayHetHan)
        {
            DataTable dataTable = new DataTable();
            try
            {

                String sql = "SELECT TOP (3) MaSanPham,TenSanPham,MaLoaiSanPham,MaTaiKhoan,GiaKhoiDiem,";
                sql += "GiaHienTai,NgayDang,NgayHetHan,MaTinhTrangSP,HinhAnh FROM SanPham WHERE MaTinhTrangSP = 1";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable; ;
        }
        public DataTable getVideo(string maSanPham)
        {
            DataTable dataTable = new DataTable();
            try
            {
                String sql = "SELECT MoTaSanPham, HinhAnh,Link FROM   Multimedia INNER JOIN ";
                sql += " SanPham_Multimedia ON Multimedia.MaMT = SanPham_Multimedia.MaMT INNER JOIN ";
                sql += " SanPham ON SanPham_Multimedia.MaSanPham = SanPham.MaSanPham WHERE SanPham.MaSanPham = '" + maSanPham + "'";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable; ;
        }
        public DataTable LayThongTinTheoMaSanPham(string masanpham)
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT * FROM SanPham where MaSanPham = '" + masanpham + "'";
                dt = DataProvider.ExecuteQuery(sql);
            }
            catch (Exception ex)
            {

            }
            return dt;
        }

        public static string LayMaSanPham(string maloai)
        {
            string selectCommand = "SELECT COUNT(*) From SanPham where MaLoaiSanPham = '" + maloai + "'";
            int num = 0;
            try
            {
                num = (int)DataProvider.ExecuteScalar(selectCommand);
            }
            catch (Exception)
            {
            }
            num++;
            string masanpham = maloai.Substring(5) + "-" + num.ToString("0000");
            return masanpham;
        }

        public static string LayMaSanPham()
        {
            long ticks = DateTime.Now.Ticks;
            return ticks.ToString();
        }

        public static string ThemSanPham(SanPhamDTO info)
        {
            string masanpham = LayMaSanPham();
            try
            {
                info.MaSanPham = masanpham;
                string sql = string.Format("insert into SanPham VALUES('{0}', N'{1}', '{2}', '{3}', '{4}', '0', '{5}', '{6}', '1', N'{7}', '{8}', 0)",
                info.MaSanPham, info.TenSanPham, info.MaLoaiSanPham, info.MaTaiKhoan, info.GiaKhoiDiem, String.Format("{0:M/d/yyyy HH:mm:ss}", info.NgayDang), String.Format("{0:M/d/yyyy HH:mm:ss}", info.NgayHetHan), info.MoTaSanPham, info.HinhAnh);
                DataProvider.ExecuteNoneQuery(sql);
                return masanpham;
            }
            catch (Exception e)
            {

            }
            return "";
        }

        public static bool HuyDauGia(string masanpham)
        {
            bool kq = false;
            try
            {

                string sql = "UPDATE SanPham SET MaTinhTrangSP = 0 WHERE MaSanPham = '" + masanpham + "'";
                DataProvider.ExecuteNoneQuery(sql);
                kq = true;
            }
            catch (Exception ex)
            {
            }
            return kq;
        }

        public static DataTable SelectSanPhamOfTrangThai(string maTaiKhoan,string trangThai)
        {
            DataTable dataTable = new DataTable();
            try
            {

                string sql = "SELECT * FROM ChiTietGiaoDich ct, SanPham sp WHERE ct.MaTaiKhoan ='" +maTaiKhoan +"' AND ct.MaSanPham = sp.MaSanPham AND ct.TinhTrang like N'"+ trangThai +"' ";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable; ;
        }
        public static List<SanPhamDTO> SelectSPOfTrangThai(string maTaiKhoan, string trangThai)
        {
            List<SanPhamDTO> sanPhamDtos = new List<SanPhamDTO>();
            try
            {

                String sql = "SELECT * FROM ChiTietGiaoDich ct, SanPham sp WHERE ct.MaTaiKhoan ='" + maTaiKhoan + "' AND ct.MaSanPham = sp.MaSanPham AND ct.TinhTrang like N'" + trangThai + "' ";
                DataTable dt  = DataProvider.ExecuteQuery(sql);

                int i, n = dt.Rows.Count;
                for (i = 0; i < n; i++)
                {
                    SanPhamDTO sp = new SanPhamDTO();
                    sp.MaSanPham = dt.Rows[i]["MaSanPham"].ToString();
                    sp.TenSanPham = dt.Rows[i]["TenSanPham"].ToString();
                    sp.MaLoaiSanPham = dt.Rows[i]["MaLoaiSanPham"].ToString();
                    sp.MaTaiKhoan = dt.Rows[i]["MaTaiKhoan"].ToString();
                    sp.GiaKhoiDiem = int.Parse(dt.Rows[i]["GiaKhoiDiem"].ToString());
                    sp.GiaHienTai = int.Parse(dt.Rows[i]["GiaHienTai"].ToString());
                    sp.NgayDang = DateTime.Parse(dt.Rows[i]["NgayDang"].ToString());
                    sp.NgayHetHan = DateTime.Parse(dt.Rows[i]["NgayHetHan"].ToString());
                    sp.MaTinhTrangSP = int.Parse(dt.Rows[i]["MaTinhTrangSP"].ToString());
                    sp.HinhAnh = dt.Rows[i]["HinhAnh"].ToString();
                    sp.SoLuotDatGia = int.Parse(dt.Rows[i]["SoLuotDatGia"].ToString());
                    sanPhamDtos.Add(sp);
                }


            }
            catch (Exception ex)
            {
                sanPhamDtos = new List<SanPhamDTO>();
            }
            return sanPhamDtos; ;
        }

        public static DataTable SelectAll(string maLoai)
        {
            DataTable dataTable = new DataTable();
            try
            {

                string sql ="SELECT lsp.TenLoaiSanPham, tk.TenTaiKhoan, sp.TenSanPham, sp.MaSanPham, lsp.MaLoaiSanPham, tk.MaTaiKhoan, ";
                sql += " sp.GiaKhoiDiem, sp.GiaHienTai, sp.NgayDang, sp.NgayHetHan, ";
                sql += " sp.MaTinhTrangSP, sp.MoTaSanPham, sp.SoLuotDatGia, sp.HinhAnh ";
                sql += " FROM LoaiSanPham lsp INNER JOIN SanPham sp ON lsp.MaLoaiSanPham = sp.MaLoaiSanPham INNER JOIN TaiKhoan tk ON sp.MaTaiKhoan = tk.MaTaiKhoan ";
                sql += " WHERE  lsp.MaLoaiSanPham = '" + maLoai + "'";
                dataTable = DataProvider.ExecuteQuery(sql);

            }
            catch (Exception ex)
            {
                dataTable = new DataTable();
            }
            return dataTable; ;
        }

        public static string GetVideo(string maSanPham)
        {
            string link = null;
            try
            {
                String sql = "SELECT Link FROM   Multimedia INNER JOIN ";
                sql += " SanPham_Multimedia ON Multimedia.MaMT = SanPham_Multimedia.MaMT INNER JOIN ";
                sql += " SanPham ON SanPham_Multimedia.MaSanPham = SanPham.MaSanPham WHERE SanPham.MaSanPham = '" + maSanPham + "'";
                DataTable dataTable = DataProvider.ExecuteQuery(sql);
                int i, n = dataTable.Rows.Count;
                 for (i = 0; i < n; i++)
                 {
                     link = dataTable.Rows[i]["Link"].ToString();
                 }
            }
            catch (Exception ex)
            {
                link = null;
            }
            return link;
        }
        // Hue
        public static bool ThayDoiPhiDAO(string _phi)
        {
            bool res = false;
            try
            {

                string sql = "UPDATE THAMSO SET GiaTri = @phi WHERE TenThamSo = 'PhiDangSanPham'";

                List<SqlParameter> sqlParameters = new List<SqlParameter>();

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@phi", SqlDbType.NVarChar);
                par[0].Value = _phi;
                sqlParameters.Add(par[0]);

                res = DataProvider.ExecuteNoneQuery(sql, sqlParameters);

            }
            catch (Exception ex)
            {
                res = false;
            }
            return res;
        }
        //Hue
        //Lấy ra phí hiện tại
        public static string LayPhiHienTaiDAO()
        {
            string tk = "";
            try
            {

                string sql = "SELECT GiaTri as 'GiaTri' FROM THAMSO WHERE TenThamSo = 'PhiDangSanPham'";
                DataTable dt = DataProvider.ExecuteQuery(sql);

                int i, n = dt.Rows.Count;
                for (i = 0; i < n; i++)
                {

                    tk = dt.Rows[i]["GiaTri"].ToString();


                }

            }
            catch (Exception ex)
            {
                tk = null;
            }
            return tk;
        }
    }
}
